package al.jdbc.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import al.jdbc.dpbridge.DataSource;
import alOmrane.jdbc.beans.Organigramme;

public class OrganigrammeDAO {
	private Connection db;
	@SuppressWarnings("unused")
	private DataSource ds;
	private Statement sql;
	
	public OrganigrammeDAO(DataSource ds){
		db = ds.getConnection();
		this.ds = ds;
		try {
			sql = db.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public List <Organigramme> findAll(){
		List <Organigramme> orgs = new ArrayList <Organigramme>();
		try {
			String query = "SELECT * FROM organigramme";

			ResultSet rs = sql.executeQuery(query);
			while (rs.next()) {
				Organigramme org = new Organigramme(Integer.parseInt(rs.getString(1)), 
						Integer.parseInt(rs.getString(2)),
						rs.getString(3),
						Integer.parseInt(rs.getString(4)),
						rs.getString(5));
				orgs.add(org);
			}
		}catch(Exception e){
			return null;
		}
		return orgs;
	}
	public List <Organigramme> findByCodeNiveau(int code_niv){
		List <Organigramme> orgs = new ArrayList <Organigramme>();
		try {
			String query = "SELECT * FROM organigramme" +
					" WHERE code_niv_organigramme="+code_niv+";";

			ResultSet rs = sql.executeQuery(query);
			while (rs.next()) {
				Organigramme org = new Organigramme(Integer.parseInt(rs.getString(1)), 
						Integer.parseInt(rs.getString(2)),
						rs.getString(3),
						Integer.parseInt(rs.getString(4)),
						rs.getString(5));
				orgs.add(org);
			}
		}catch(Exception e){
			return null;
		}
		return orgs;
	}
	
	public Organigramme findById(int id){
		try {
			String query = "SELECT * FROM organigramme" +
					" WHERE id_organigramme="+id+";";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			Organigramme org = new Organigramme(Integer.parseInt(rs.getString(1)), 
						Integer.parseInt(rs.getString(2)),
						rs.getString(3),
						Integer.parseInt(rs.getString(4)),
						rs.getString(5));
			return org;
		}catch(Exception e){
			return null;
		}
	}
	public int getNivCount(){
		try {
			String query = "SELECT MAX(code_niv_organigramme) FROM organigramme";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			return Integer.parseInt(rs.getString(1));
		}catch(Exception e){
			return 0;
		}
	}
	public boolean hasChilds(Organigramme o){
		try {
			String query = "SELECT COUNT(*) FROM organigramme" +
					" WHERE code_sup="+o.getCode_niv()+";";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			return ((Integer.parseInt(rs.getString(1)))>0);
		}catch(Exception e){
			return false;
		}
	}
	public List<Organigramme> getChilds(Organigramme o){
		List <Organigramme> orgs = new ArrayList <Organigramme>();
		try {
			String query = "SELECT * FROM organigramme" +
					" WHERE lib_sup='"+o.getLib_niv()+"';";

			ResultSet rs = sql.executeQuery(query);
			
			while (rs.next()) {
				Organigramme org = new Organigramme(Integer.parseInt(rs.getString(1)), 
						Integer.parseInt(rs.getString(2)),
						rs.getString(3),
						Integer.parseInt(rs.getString(4)),
						rs.getString(5));
				orgs.add(org);
				
			}
		}catch(Exception e){
			return null;
		}
		return orgs;
	}
}
